--Function of adding enum values constraint
create or replace procedure p_create_enum_constraint(
       --Column id
       a_column_id raw)
as
    --Table id
    a_table_id raw(16);    
    --Table name
    a_table_name varchar2(26);
    --Column name
    a_column_name varchar2(26);
    --Enum value list
    a_enum_value_list varchar2(32767) := '';
begin
    
    --Reading column properties
    select table_id, column_name
    into a_table_id, a_column_name
    from meta_enum_columns
    where column_id = a_column_id;
    
    a_table_name := p_get_table_name(a_table_id);
    
    --Searching enum values
    for val in (select value from meta_enum_members
     where column_id = a_column_id) loop
             
        if trim(a_enum_value_list) is not null then
            a_enum_value_list := a_enum_value_list || ',';
        end if;
                     
        a_enum_value_list := a_enum_value_list || val.value;
             
    end loop;
         
    if trim(a_enum_value_list) is not null then     
        --Adding enum column constraint
        execute immediate 'alter table tbl_' || a_table_name
        || ' add constraint m' || substr(a_column_id, 1, 29)
        || ' check(' || a_column_name
        || '= any(' || a_enum_value_list || '))';
    else
         --(so, one should specify enum members before inserting)
         execute immediate 'alter table tbl_' || a_table_name
         || ' add constraint m' || substr(a_column_id, 1, 29)
         || ' check(' || a_column_name || ' is null)';
    end if;
end;

--go

--Function af dropping enum values constraint
create or replace procedure p_drop_enum_constraint(
       --Column id
       a_column_id raw)
as
    --Table id
    a_table_id raw(16);
    --Table name
    a_table_name varchar2(26);
begin

    --Reading column properties
    select table_id into a_table_id
    from meta_enum_columns
    where column_id = a_column_id;
    
    a_table_name := p_get_table_name(a_table_id);
     
    execute immediate 'alter table tbl_' || a_table_name
    || ' drop constraint m' || substr(a_column_id, 1, 29);
     
end;

--go

--Function of creating enum column
create or replace procedure meta_add_enum_column(
    --Table id
    a_table_id raw,
    --Column id
    a_column_id raw,
    --Column name
    a_column_name varchar2)
as
    --Table name
    a_table_name varchar2(26);
begin
    p_check_col_not_exists(a_table_id, a_column_id, a_column_name);

    a_table_name :=  p_get_table_name(a_table_id);
    
    --Changing the metadata
    insert into meta_enum_columns(column_id, table_id, column_name,
        order_index, customization_level)
    values (a_column_id, a_table_id, a_column_name,
        p_generate_order_index(a_table_id), 1);

    --Dropping table view
    p_drop_view_if_exists(a_table_name);
         
   execute immediate 'alter table tbl_' 
    || a_table_name
    || ' add ' || a_column_name
    || ' number(10)';
         
    --Creating enum values constraint
    p_create_enum_constraint(a_column_id);
         
    --Creating table view
    p_create_table_view(a_table_id);
    
end;

--go

--Function of adding new enum value
create or replace procedure meta_add_enum_member(
       --Column id
       a_column_id raw,
       --Value
       a_value number)
as
    --Row count
    a_row_count number;
begin
    
    --Searching for enum member
    select count(*)
    into a_row_count
    from meta_enum_members
    where column_id = a_column_id and value = a_value;
    
    if a_row_count <> 0 then
        raise_application_error(-20054,
                'A member with the specified value was already added (column_id = '
                || a_column_id || ', value = ' || a_value
                || ') (#error_code = 20054)');
    end if;

    --Changing the metadata
    insert into meta_enum_members (column_id, value)
    values(a_column_id, a_value);

    --Dropping enum constraint
    p_drop_enum_constraint(a_column_id);
         
    --Creating new enum constraint
    p_create_enum_constraint(a_column_id);

end;

--go

--Function of removing enum value
create or replace procedure meta_delete_enum_member(
    --Column id
    a_column_id raw,
    --Value
    a_value number)
as
    --Table id
    a_table_id raw(16);
    --Table name
    a_table_name varchar2(26);
    --Column name
    a_column_name varchar2(26);
    --Default value
    a_default_value number(10);
    --Row count
    a_row_count number;
begin
    
    --Searching for enum member
    select count(*)
    into a_row_count
    from meta_enum_members
    where column_id = a_column_id and value = a_value;
    
    if a_row_count = 0 then
        raise_application_error(-20018,
                'Deleting nonexistent enumeration member is forbidden (column_id = '
                || a_column_id
                || ') (#error_code = 20018)');
    end if;

    --Reading column properties
    select table_id, column_name, default_value
    into a_table_id, a_column_name, a_default_value
    from meta_enum_columns
    where column_id = a_column_id;
    
    a_table_name := p_get_table_name(a_table_id);
    
    --If default value was deleted
     if a_value = a_default_value then
        --Setting default value to null
        meta_set_enum_default(a_column_id, null);
     end if;
    
     --Dropping enum constraint
     p_drop_enum_constraint(a_column_id);
             
     --Setting deleted member values to null
     execute immediate 'update tbl_' || a_table_name
     || ' set ' || a_column_name
     || ' = null where ' || a_column_name || ' = :1'
     using a_value;
     
     --Creating new enum constraint
     p_create_enum_constraint(a_column_id);
     
     --Changing the metadata
    delete from meta_enum_members
    where column_id = a_column_id
    and value = a_value;           
end;

--go

--Function of changing enum member value
create or replace procedure meta_set_enum_member_value(
    --Column name
    a_column_id raw,
    --Old value
    a_old_value number,
    --New value
    a_new_value number)
as
    --Table id
    a_table_id raw(16);
    --Table name
    a_table_name varchar2(26);
    --Column name
    a_column_name varchar2(26);
    --Default value
    a_default_value number(10);
begin

    if a_new_value = a_old_value then
        return;
    end if;
        
    --Reading column properties
    select table_id, column_name, default_value
    into a_table_id, a_column_name, a_default_value
    from meta_enum_columns
    where column_id = a_column_id;
        
    a_table_name := p_get_table_name(a_table_id);
    
    --If default value is equal to old value
    if a_default_value = a_old_value then
        --Setting default value to null
        meta_set_enum_default(a_column_id, null);
    end if;
        
    --Dropping old enum constraint
    p_drop_enum_constraint(a_column_id);
                 
    --Updating old member values
    execute immediate 'update tbl_' || a_table_name
    || ' set ' || a_column_name
    || ' = :1 where ' || a_column_name || ' = :2'
    using a_new_value, a_old_value;
    
    --Changing the metadata
    update meta_enum_members
    set value = a_new_value
    where column_id = a_column_id and value = a_old_value;
         
    --Creating new enum constraint
    p_create_enum_constraint(a_column_id);
    
    --If default value is equal to old value
    if a_default_value = a_old_value then
        --Setting default value to new value
        meta_set_enum_default(a_column_id, a_new_value);
    end if;
     
end;

--go

--Function of setting enum column default value
create or replace procedure meta_set_enum_default(
    --Column id
    a_column_id raw,
    --Default value
    a_default_value number)
as
    --Table id
    a_table_id raw(16);
    --Table name
    a_table_name varchar2(26);
    --Column name
    a_column_name varchar2(26);
begin
    p_check_col_type(a_column_id, 'meta_enum_columns', 'enumeration');
    
    --Reading column properties
    select table_id, column_name
    into a_table_id, a_column_name
    from meta_enum_columns
    where column_id = a_column_id;
    
    a_table_name := p_get_table_name(a_table_id);
    
     if a_default_value is not null then
        execute immediate 'alter table tbl_' 
        || a_table_name
        || ' modify (' || a_column_name
        || ' default ''' || a_default_value || ''')';
    else
        execute immediate 'alter table tbl_' 
        || a_table_name
        || ' modify (' || a_column_name
        || ' default null)';
    end if;
    
    --Updating the metadata
    update meta_enum_columns
    set default_value = a_default_value
    where column_id = a_column_id;
end;

--go

--Function of enabling/disabling enum value checking
create or replace procedure meta_set_enum_value_checking(
    --The column id
    a_column_id raw,
    --The need for enum value checking
    a_value_checking number)
as
    --Table name
    a_table_name varchar2(26);
begin
    p_check_col_type(a_column_id, 'meta_enum_columns', 'enumeration');
    
    --Reading the table name
    select table_name into a_table_name
    from meta_table_columns
    where column_id = a_column_id;
    
    --If value checking is enabled
    if a_value_checking = 1 then
        execute immediate 'alter table tbl_' || a_table_name
        || ' enable constraint m' || substr(a_column_id, 1, 29);
    --If value checking is disabled
    else
         execute immediate 'alter table tbl_' || a_table_name
        || ' disable constraint m' || substr(a_column_id, 1, 29);
    end if;
    
    --Updating the metadata
    update meta_enum_columns
    set value_checking = a_value_checking
    where column_id = a_column_id;
end;

--go